from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from datetime import datetime, timedelta
from sql import MySQLDB
from myexcel import *
from mytools import *
from config import *
import tkinter.font
import tkinter.messagebox as messagebox
import openpyxl,concurrent.futures
import time,os,subprocess,uuid,traceback


class HomeFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        dbdiv=DBFrame(self)
        dbdiv.pack(pady=3,fill=X,padx=3)
        TMGGFrame(self,dbdiv).pack(pady=3,fill=X,padx=3)
        CatCSFrame(self,dbdiv).pack(pady=3,fill=X,padx=3)
        DCFrame(self,dbdiv).pack(pady=3,fill=X,padx=3)


class DBFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.login=False
        self.tables=[]
        self.createWidgets()
        Thread(target=self.getDB).start()

    def createWidgets(self):
        basic_info=get_allconfig()
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='数据库信息', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='服务器地址:').pack(side=LEFT, padx=2, pady=2)
        self.DBHostIpt = Entry(f00,width=10)
        self.DBHostIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBHostIpt.insert(0,basic_info['DB_HOST'])


        Label(f00, text='数据库账户:').pack(side=LEFT, padx=2, pady=2)
        self.DBUidIpt = Entry(f00,width=5)
        self.DBUidIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBUidIpt.insert(0,basic_info['DB_UID'])

        
        Label(f00, text='数据库密码:').pack(side=LEFT, padx=2, pady=2)
        self.DBPwdIpt = Entry(f00)
        self.DBPwdIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBPwdIpt.insert(0,basic_info['DB_PWD'])
        
        Label(f00, text='服务器端口:').pack(side=LEFT, padx=2, pady=2)
        self.DBPortIpt = Entry(f00,width=5)
        self.DBPortIpt.pack(side=LEFT, padx=2, pady=2)
        self.DBPortIpt.insert(0,basic_info['DB_PORT'])
        

        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)
        
        Label(f01, text='选择数据库:').pack(side=LEFT, padx=2, pady=2)
        self.DBSelect = ttk.Combobox(f01,state='readonly')
        self.DBSelect['value'] = ['未加载']
        self.DBSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.change_db).start())
        self.DBSelect.pack(side=LEFT, padx=2, pady=2)
        self.DBSelect.current(0)

        self.btn_conenct=Button(f01,text='连接接服务器',command=lambda:Thread(target=self.connect_server).start(),fg='#ffffff',bg='green')
        self.btn_conenct.pack(side=LEFT,padx=2, pady=2)


    def getDB(self):

        basic_info=get_allconfig()
        host=basic_info['DB_HOST']
        uid=basic_info['DB_UID']
        pwd=basic_info['DB_PWD']
        port=basic_info['DB_PORT']
        dbnames=list_databases(host,uid,pwd,port)
        
        if dbnames:
            dbnames_lower=[dbname.lower() for dbname in dbnames]
            self.DBSelect['value']=dbnames
            select_db_name=basic_info['DB_NAME']
            if select_db_name.lower() in dbnames_lower:
                select_ind=dbnames_lower.index(select_db_name.lower())
                self.DBSelect.current(select_ind)
                self.login=True
            else:
                self.DBSelect.current(0)
            
            cur_db_name=self.DBSelect.get()
            print(f'当前选择数据库:{cur_db_name}')
            try:
                db=MySQLDB(cur_db_name,uid,pwd,host,port)
                self.tables=db.get_all_tables()
                db.close_connection()
            except Exception as e:
                print(f'初始化链接数据库错误 => {e}')
                traceback.print_exc()
                
        
    def connect_server(self):

        self.btn_conenct
        host=self.DBHostIpt.get().strip()
        uid=self.DBUidIpt.get().strip()
        pwd=self.DBPwdIpt.get().strip()
        port=int(self.DBPortIpt.get().strip())
        basic_info=get_allconfig()
        if not(host and uid and pwd and port):
            messagebox.showerror('提示','请输入完整信息')

        self.btn_conenct.config(state=DISABLED)
        select_db_name=basic_info['DB_NAME']
        dbnames=list_databases(host,uid,pwd,port)
        
        has_exist_db=False
        if dbnames:
            dbnames_lower=[dbname.lower() for dbname in dbnames]
            if select_db_name.lower() in dbnames_lower:
                has_exist_db=True
        else:
            dbnames=[]
        if not has_exist_db:
            create_database(host,uid,pwd,select_db_name)
            dbnames=[select_db_name]+dbnames

        self.DBSelect['value']=dbnames
        self.DBSelect.current(0)
        cur_db_name=self.DBSelect.get()
        update_config({'DB_NAME':cur_db_name,'DB_UID':uid,'DB_PWD':pwd,'DB_HOST':host,'DB_PORT':port})
        print(f'服务器《{host}》成功链接')
        messagebox.showinfo(f'提示',f'服务器《{host}》成功链接')
        
        self.btn_conenct.config(state=NORMAL)

    def get_conn(self):
        for jjj in range(3):
            try:
                cur_db_name=self.DBSelect.get()
                if cur_db_name =='未加载':
                    basic_info=get_allconfig()

                    cur_db_name=basic_info['DB_NAME']
                    host=basic_info['DB_HOST']
                    uid=basic_info['DB_UID']
                    pwd=basic_info['DB_PWD']
                    port=basic_info['DB_PORT']
                    return MySQLDB(cur_db_name,uid,pwd,host,port)
                else:
                    host=self.DBHostIpt.get().strip()
                    uid=self.DBUidIpt.get().strip()
                    pwd=self.DBPwdIpt.get().strip()
                    port=int(self.DBPortIpt.get().strip())
                    return MySQLDB(cur_db_name,uid,pwd,host,port)
            except Exception as e:
                print(f'链接服务器错误 => {e},正在重试...')
    

    def change_db(self):
        print(f'正在切换数据库...')
        db=self.get_conn()
        cur_db_name=self.DBSelect.get()
        self.tables=db.get_all_tables()
        db.close_connection()
        print(f'成功切换到数据库:{cur_db_name}')
        update_config({'DB_NAME':cur_db_name})

class TMGGFrame(Frame):
    def __init__(self,master,dbfra:DBFrame):
        Frame.__init__(self,master)
        self.dbfra=dbfra
        self.session=requests.Session()
        self.createWidgets()
    
    def createWidgets(self):
        basic_info=get_allconfig()
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集公司名字', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        label_font = tkinter.font.Font(family="微软雅黑", size=10, weight="bold")
        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)

        Label(f00, text='采集公司类型:').pack(side=LEFT, padx=2, pady=2)
        self.CJModeSelect = ttk.Combobox(f00,width=8,state='readonly')
        self.CJModeSelect['value'] = ['注册','注销']
        self.CJModeSelect.pack(side=LEFT, padx=5, pady=2)
        self.CJModeSelect.current(basic_info['LAST_CJ_TYPE'])


        Label(f00, text='上次采集页数:').pack(side=LEFT, padx=2, pady=2)
        self.FirstPageIpt = Entry(f00,width=5)
        self.FirstPageIpt.pack(side=LEFT, padx=2, pady=2)
        self.FirstPageIpt.insert(0,basic_info['LAST_CJ_PAGE'])
        Label(f00, text='最大采集页数:').pack(side=LEFT, padx=2, pady=2)
        self.MaxPageIpt = Entry(f00,width=7)
        self.MaxPageIpt.pack(side=LEFT, padx=2, pady=2)
        self.MaxPageIpt.insert(0,basic_info['CJ_NAME_MAX_PAGE'])

        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)
        Label(f01, text='循环任务:',font=label_font).pack(side=LEFT, padx=2)
        self.LoopSelect = ttk.Combobox(f01,state="readonly",width=6)
        self.LoopSelect['value']=['否','是']
        self.LoopSelect.pack(side=LEFT, padx=2)
        self.LoopSelect.current(basic_info['CJ_NAME_LOOP'])

        Label(f01, text='循环间隔:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SleepHourIpt = Entry(f01,width=7)
        self.SleepHourIpt.pack(side=LEFT, padx=2, pady=2)
        self.SleepHourIpt.insert(0,basic_info['CJ_NAME_LOOP_SLEEP'])
        Label(f01, text='小时').pack(side=LEFT, padx=2)

        Label(f01, text='定时任务:',font=label_font).pack(side=LEFT, padx=2)
        self.TimingSelect = ttk.Combobox(f01,state="readonly",width=6)
        self.TimingSelect['value']=['无定时']+[str(j) for j in range(0,24)]
        self.TimingSelect.pack(side=LEFT, padx=2)
        self.TimingSelect.current(basic_info['CJ_NAME_TIMING'])

        f02=Frame(f1)
        f02.pack(fill=X,padx=2,pady=2)
        self.btn_cj=Button(f02,text='开始采集',command=lambda:Thread(target=self.begin_cj).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT,padx=2, pady=2)


    def begin_cj(self):
       
        db = self.dbfra.get_conn()
        if not db:
            messagebox.showerror('提示','请正确连接数据库')
            return

        page=self.FirstPageIpt.get().strip()
        maxpage=self.MaxPageIpt.get().strip()
        sleep_hour=self.SleepHourIpt.get().strip()
        if not page or not maxpage or not sleep_hour:
            messagebox.showinfo('提示','请先设置采集页数')
            return
        basic_info=get_allconfig()
        cj_type=self.CJModeSelect.current()
        is_loop=self.LoopSelect.current()
        timing=self.TimingSelect.current()
        tb_name=basic_info['TABLE_COMPANY_LOGIN'] if cj_type==0 else basic_info['TABLE_COMPANY_LOGOUT']

        update_config({
            'LAST_CJ_TYPE':cj_type,
            'CJ_NAME_LOOP':is_loop,
            'CJ_NAME_LOOP_SLEEP':float(sleep_hour),
            'CJ_NAME_TIMING':timing

        })

        if not db.table_exists(tb_name):
            
            db.create_table(tb_name,COMPANY_NAME_COL,[('name','code')],['state','isuse'])

        db.close_connection()


        self.btn_cj.config(state=DISABLED)

        session=self.session
        headers={
            'Host':'dynamic.gs1cn.org',
            'Origin':'http://www.ancc.org.cn',
            'Token':'null',
            'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36'
        }
        page=int(page)
        maxpage=int(maxpage)
        logoutFlag=self.CJModeSelect.current()
        
        rwname = '采集公司名字'
        loop_count=0
        first_page=page
        while True:

            print(f"《{rwname}》,公司类型:{self.CJModeSelect.get()},页数范围:{first_page}~{maxpage}页,是否循环:{is_loop},定时:{self.TimingSelect.get()}")

            if timing>0:
                now = datetime.now()
                hour=timing-1
                # 确定目标时间：如果当天的指定时间还未到，设为今天，否则设为明天
                if now.hour < hour or (now.hour == hour and now.minute == 0 and now.second == 0):
                    next_run = now.replace(hour=hour, minute=0, second=0, microsecond=0)
                else:
                    next_run = (now + timedelta(days=1)).replace(hour=hour, minute=0, second=0, microsecond=0)
                
                # 计算等待时间，并确保为非负数
                sleep_time = (next_run - now).total_seconds()
                if sleep_time <= 0:
                    print("目标时间已经过期，跳过等待")
                    time.sleep(20)
                    continue
                print(f"距离下次运行任务《{rwname}》还有 {sleep_time / 3600:.2f} 小时")
                # 等待到指定时间
                time.sleep(sleep_time)
            j=0
            
            while True:
                data={
                'limit':500,
                'logoutFlag' :logoutFlag,
                'page': page,

                }

                try:
                    res=session.post('http://dynamic.gs1cn.org/office/eanupc/api/getEanUpcByCondition',json=data,headers=headers,timeout=30)
                    jsondata=res.json()
                    if jsondata['msg']=='success':
                        curdata=jsondata['data']
                        if j==0:
                            totalPage=curdata['totalPage']
                            update_config({'CJ_NAME_MAX_PAGE':totalPage})
                            print(f'共{totalPage}页')
                        currPage=curdata['currPage']
                        if page>totalPage or page>maxpage:
                            break
                        
                        plist=jsondata['data']['list']
                        db = self.dbfra.get_conn()
                        aff=0

                        for item in plist:
                            rs= db.query(f'select count(*) from {tb_name} where code=%s and name=%s',(item['code'],item['firmName']))
                            if rs[0][0]==0:
                                zdstr=','.join(['code','loginDate','logDate','logoutFlag','name','fid'])
                                params=[item['code'],item['loginDate'],item['logDate'],item['logoutFlag'],item['firmName'],item['fid']]
                                wstr=','.join([r'%s' for _ in params])
                                sqlstr=f'insert into {tb_name}({zdstr}) values({wstr})'
                                aff+=db.execute_command(sqlstr,params)

                        print(f'第{page}页,《{self.CJModeSelect.get()}》采集到 {len(plist)} 个公司,存入数据库:{aff}个,剩余:{maxpage-page}/{maxpage}')
                        db.close_connection()
                    if currPage ==totalPage:
                        break
                    page+=1
                    j+=1
                    update_config({'LAST_CJ_PAGE':page})
                except Exception as e:
                    print(f'第{page}页,《{self.CJModeSelect.get()}》,出现错误 =>{e}')
            
            loop_count+=1
            print(f'第{loop_count}轮循环,共采集《{self.CJModeSelect.get()}》,共采集{j}页,等待{sleep_hour}小时,开启下一轮采集...')
            if not is_loop:
                break
            else:
                time.sleep(float(sleep_hour)*3600)
                page=first_page

        messagebox.showinfo('提示',f'《{self.CJModeSelect.get()}》,共循环采集{j}页')
        self.btn_cj.config(state=NORMAL)

class CatCSFrame(Frame):
    def __init__(self,master,dbfra:DBFrame):
        Frame.__init__(self,master)
        self.dbfra=dbfra
        self.ACount=StringVar()
        self.ACount.set('当前未导入名称表')
        self.yzstatusStr=StringVar()
        self.yzstatusStr.set('未开启验证码工具')
        self.CCNameCount=StringVar()
        self.CCNameCount.set('正在获取公司名称...')
        self.InfoCount=StringVar()
        self.InfoCount.set('正在获取公司详细信息...')
        self.session=requests.Session()
        self.isEnded=False
        self.createWidgets()

        Thread(target=self.get_tab_list).start()
        Thread(target=self.getYzmStatus).start()
        Thread(target=self.getCCnameCount).start()
        Thread(target=self.getInfo).start()

    def createWidgets(self):
        label_font = tkinter.font.Font(family="微软雅黑", size=10, weight="bold")
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集公司详细信息', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)

        Label(f00, text='选择公司名字数据表:').pack(side=LEFT, padx=2, pady=2)
        self.TabNameSelect = ttk.Combobox(f00,width=15)
        self.TabNameSelect['value'] = ['正在加载数据表']
        self.TabNameSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.change_tab,args=('name',)).start())
        self.TabNameSelect.pack(side=LEFT, padx=5, pady=2)
        self.TabNameSelect.current(0)

        Label(f00, text='EXCEL路径:').pack(side=LEFT, padx=2, pady=2)
        self.ApathIpt = Entry(f00)
        self.ApathIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00, text='选择路径', command=self.choosefileA).pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.ACount,fg='red').pack(side=LEFT, padx=2, pady=2)

        self.btn_cj=Button(f00,text='导入公司名称',command=lambda:Thread(target=self.begin_dr).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT, padx=2, pady=2)

        f04=Frame(f1)
        f04.pack(fill=X)
        Label(f04, textvariable=self.CCNameCount,fg='red').pack(side=LEFT, padx=2, pady=2)


        f01=Frame(f1)
        f01.pack(fill=X,padx=2,pady=2)

        Label(f01, text='选择公司信息数据表:').pack(side=LEFT, padx=2, pady=2)
        self.TabInfoSelect = ttk.Combobox(f01,width=15)
        self.TabInfoSelect['value'] = ['正在加载数据表']
        self.TabInfoSelect.bind("<<ComboboxSelected>>", lambda e:Thread(target=self.change_tab,args=('info',)).start())
        self.TabInfoSelect.pack(side=LEFT, padx=5, pady=2)
        self.TabInfoSelect.current(0)

        Label(f01, text='采集模式:').pack(side=LEFT, padx=2, pady=2)
        self.CJModeSelect = ttk.Combobox(f01,width=15)
        self.CJModeSelect['value'] = ['只增加新数据','更新并增加新数据']
        self.CJModeSelect.pack(side=LEFT, padx=5, pady=2)
        self.CJModeSelect.current(0)

        Label(f01, text='线程数:').pack(side=LEFT, padx=2, pady=2)
        self.XXNumSelect = ttk.Combobox(f01,width=5)
        self.XXNumSelect['value'] = [1,3,5,10]
        self.XXNumSelect.pack(side=LEFT, padx=5, pady=2)
        self.XXNumSelect.current(0)

        f02=Frame(f1)
        f02.pack(fill=X,padx=2,pady=2)
        self.btn_yz=Button(f02,text='开启验证码工具',command=lambda:Thread(target=self.start_yz).start(),fg='#ffffff',bg='green')
        self.btn_yz.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.yzstatusStr,fg='red').pack(side=LEFT, padx=2, pady=2)



        f03=Frame(f1,padx=2,pady=2)
        f03.pack(fill=X)

        Label(f03, text='定时任务:',font=label_font).pack(side=LEFT, padx=2)
        self.TimingSelect = ttk.Combobox(f03,state="readonly",width=6)
        self.TimingSelect['value']=['无定时']+[str(j) for j in range(0,24)]
        self.TimingSelect.pack(side=LEFT, padx=2)
        self.TimingSelect.current(get_config('CJ_INFO_TIMING'))

        self.btn_cj=Button(f03,text='开始采集',command=lambda:Thread(target=self.begin_cj).start(),fg='#ffffff',bg='green')
        self.btn_cj.pack(side=LEFT, padx=2, pady=2)

        self.btn_end_cj=Button(f03,text='结束采集',command=self.end_cj,fg='#ffffff',bg='green')
        self.btn_end_cj.pack(side=LEFT, padx=2, pady=2)
        Label(f03, textvariable=self.InfoCount,fg='red').pack(side=LEFT, padx=2, pady=2)
        



    def get_tab_list(self):
        
        while True:
            try:
                basic_info=get_allconfig()
                tb1=basic_info['TABLE_NAME_SELECT']
                tb2=basic_info['TABLE_INFO_SELECT']
                old_tabs=self.TabNameSelect['values']
                tabs=self.dbfra.tables
                
                if not tabs:
                    tabs=['当前无数据表']
                if old_tabs!=tuple(tabs):
                    
                    self.TabNameSelect['values']=tabs
                    tabs_lower=[tb.lower() for tb in tabs]
                    
                    if tb1.lower() in tabs_lower:
                        self.TabNameSelect.current(tabs_lower.index(tb1.lower()))
                    else:
                        self.TabNameSelect.current(0)
                    self.TabInfoSelect['values']=tabs
                    if tb2.lower() in tabs_lower:
                        self.TabInfoSelect.current(tabs_lower.index(tb2.lower()))
                    else:
                        self.TabInfoSelect.current(0)
                    
            except Exception as e:
                print(e)
                traceback.print_exc()
            
            time.sleep(1)
        
    def getYzmStatus(self):
        while True:
            try:
                if check_port_in_use(1688) or is_process_running(process_name='startYzm.exe'):
                    self.yzstatusStr.set('验证码工具已开启')
                    self.btn_yz.config(state=DISABLED)
                else:
                    self.yzstatusStr.set('验证码工具未开启')
                    self.btn_yz.config(state=NORMAL)
                
            except Exception as e:
                print(e)
            time.sleep(5)

    def getCCnameCount(self):
        
        while True:
            tab_name=self.TabNameSelect.get()
            try:
                
                if tab_name not in ['正在加载数据表','当前无数据表']:
                    db = self.dbfra.get_conn()
                    if not db.table_exists(tab_name):
                        db.create_table(tab_name,COMPANY_NAME_COL,[('name','code')],['state','isuse'])
                        self.dbfra.change_db()
                    rs1=db.query(f'select count(*) from {tab_name} where state=0')
                    rs2=db.query(f'select count(*) from {tab_name} where state=1')
                    self.CCNameCount.set(f'当前数据库共 {rs1[0][0]+rs2[0][0]} 个产商名称,其中已采集:{rs2[0][0]} 个,未采集:{rs1[0][0]} 个')
                    db.close_connection()
                else:
                    self.CCNameCount.set(f'正在获取公司名称...')

            except Exception as e:
                print(f'加载公司名称表错误 => {e}')
            
            time.sleep(3)
        
    def getInfo(self):
        while True:
            tab_name=self.TabInfoSelect.get()
            try:
                if tab_name not in ['正在加载数据表','当前无数据表']:
                    db = self.dbfra.get_conn()
                    if not db.table_exists(tab_name):
                        db.create_table(tab_name,COMPANY_COL,[('_id','firm_name')],['daochu','isuse','pon'])
                        self.dbfra.change_db()
                    rs1=db.query(f'select count(*) from {tab_name} where daochu=0')
                    rs2=db.query(f'select count(*) from {tab_name} where daochu>0')
                    self.InfoCount.set(f'当前数据库已采集公司信息 {rs1[0][0]+rs2[0][0]} 个,其中已导出:{rs2[0][0]} 个,未导出:{rs1[0][0]} 个')
                else:
                    self.InfoCount.set(f'正在获取公司详细信息...')

            except Exception as e:
                print(f'加载公司详细表错误 => {e}')
            time.sleep(3)
    
    def change_tab(self,tab_type):

        if tab_type=='name':
            tab_name=self.TabNameSelect.get()
            if not self.ck_tab(tab_name,tab_type):
                print(f'表《{tab_name}》不是公司名称表')
                tab_names=self.TabNameSelect['value']
                for jjj in range(len(tab_names)):
                    cur_tab_name=tab_names[jjj]
                    if self.ck_tab(cur_tab_name,tab_type):
                        self.TabNameSelect.current(jjj)
                        break
                messagebox.showerror('提示',f'表《{tab_name}》不是公司名称表')
                

        elif tab_type=='info':
            tab_name=self.TabInfoSelect.get()
            if not self.ck_tab(tab_name,tab_type):
                print(f'表《{tab_name}》不是公司名称表')
                tab_names=self.TabInfoSelect['value']
                for jjj in range(len(tab_names)):
                    cur_tab_name=tab_names[jjj]
                    if self.ck_tab(cur_tab_name,tab_type):
                        self.TabInfoSelect.current(jjj)
                        break
                messagebox.showerror('提示',f'表《{tab_name}》不是公司详细信息表')
    
    def ck_tab(self,tname,tab_type):
        db=self.dbfra.get_conn()
        need_col=set(COMPANY_NAME_COL.keys()) if tab_type=='name' else set(COMPANY_COL.keys())
        select_col=set(db.get_table_columns(tname))
        db.close_connection()
        return need_col<=select_col

    def choosefileA(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.ApathIpt.delete(0, END)
            self.ApathIpt.insert(0, select_path)
            self.afpath = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            ind=None
            for col in range(ws.max_column):
                ctit=ws.cell(1,col+1).value
                if ctit:
                    if '公司名称' in ctit or 'firm_name' in ctit:
                        ind=col
                        break
            if ind:
                self.ACount.set(f'共 {max_row-1} 行,公司名称在第{ind+1}列')
                self.ind=ind
            else:
                self.ApathIpt.delete(0, END)
                self.ACount.set(f'表格错误,当前表格没有包含《公司名称》的列')
                
        else:

            messagebox.showerror('错误提示', '请选择excel文件')
    
    def start_yz(self):
        if check_port_in_use(1688) or is_process_running(process_name='startYzm.exe'):
            messagebox.showinfo('提示','验证码软件正在运行')
            self.btn_yz.config(state=DISABLED)
            return

        self.btn_yz.config(state=DISABLED)
        print('正在启动验证码软件...')
        exe_path = os.path.join(YZ_PATH[0], YZ_PATH[1])
        subprocess.Popen(exe_path)

    def begin_dr(self):
        
        def upload_name(cr,cname):
            db=MySQLDB(DB_PATH)
            rs=db.query(f'select count(*) from {TABLE_NAME_CHANG_SHANG} where name=%s',(cname,))
            if rs[0][0]>0:
                db.close_connection()
                return cr,cname,'已存在'
            else:
                aff= db.execute_command(f'insert into {TABLE_NAME_CHANG_SHANG}(name) values(%s)',(cname,))
                db.close_connection()
                return cr,cname,aff
            
        if not self.ApathIpt.get().strip():
            messagebox.showinfo('提示','请先导入文件')
            return
        pdatas=getDatasByEecel(self.afpath)
        task_count=len(pdatas)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:
                futures.append(executor.submit(upload_name,row,pdata[self.ind]))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_name,res_aff = future.result()
                    print(f'第 {res_row} 行,公司名称:{res_name},导入状态:{res_aff},任务队列剩余:{task_count}')
                except Exception as exc:
                    print(f"线程出错: {exc},任务队列剩余:{task_count}")
                task_count-=1
            
        print(f'公司名称全部上传完毕')
        messagebox.showinfo('提示','公司名称全部上传完毕')

    def begin_cj(self):

        if not check_port_in_use(1688):
            messagebox.showinfo('提示','请先点击验证码软件的启动键')
            return
        
        tab_name=self.TabNameSelect.get()
        tab_info=self.TabInfoSelect.get()
        timing=self.TimingSelect.current()
        if not messagebox.askyesno('提示',f'名称表:《{tab_name}》,详细表:《{tab_info}》,开始采集?'):
            return
        self.btn_cj.config(state=DISABLED)
        rwname='采集公司详细信息'

        update_config({
            'TABLE_NAME_SELECT':tab_name,
            'TABLE_INFO_SELECT':tab_info,
            'CJ_INFO_TIMING':timing
        })

        while True:
            try:
                db=self.dbfra.get_conn()
                if timing>0:
                    now = datetime.now()
                    hour=timing-1
                    # 确定目标时间：如果当天的指定时间还未到，设为今天，否则设为明天
                    if now.hour < hour or (now.hour == hour and now.minute == 0 and now.second == 0):
                        next_run = now.replace(hour=hour, minute=0, second=0, microsecond=0)
                    else:
                        next_run = (now + timedelta(days=1)).replace(hour=hour, minute=0, second=0, microsecond=0)
                    
                    # 计算等待时间，并确保为非负数
                    sleep_time = (next_run - now).total_seconds()
                    if sleep_time <= 0:
                        print("目标时间已经过期，跳过等待")
                        time.sleep(20)
                        continue
                    print(f"距离下次运行任务《{rwname}》还有 {sleep_time / 3600:.2f} 小时")
                    # 等待到指定时间
                    time.sleep(sleep_time)

                kws=db.query(f'select id,name from {tab_name} where state=0')
                task_count=len(kws)

                with concurrent.futures.ThreadPoolExecutor(max_workers=self.XXNumSelect.current()+1) as executor:
                    futures=[]

                    for pdata in kws:
                        futures.append(executor.submit(self.get_list,pdata))

                    for future in concurrent.futures.as_completed(futures):
                        task_count-=1
                        try:
                            aff2=0
                            res_pid,res_pname,res_aff = future.result()
                            if res_aff>0:
                                aff2=db.execute_command(f'update {tab_name} set state=1 where id=%s',(res_pid,))
                            print(f'id:{res_pid} 行,搜索名称:《{res_pname}》,保存行数:{res_aff},标记名称状态:{aff2},采集队列剩余:{task_count}')

                        except Exception as exc:
                            print(f"线程出错: {exc},采集队列剩余:{task_count}")
                            time.sleep(2)
                        
                db.close_connection()

            except Exception as e:
                print(e)
            print(f'本次采集完毕,等待5分钟下一次监测...')
            if self.isEnded:
                break
        self.isEnded=False
        messagebox.showinfo('提示','本次采集完毕')
        self.btn_cj.config(state=NORMAL)

    def end_cj(self):

        self.isEnded=True

    def get_list(self,pitem):
        if self.isEnded:
            return (0,0,0)
        pid,pname=pitem
        session=self.session
        tab_info_name=self.TabInfoSelect.get()
        headers={
            'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36'
        }
        random_uuid = str(uuid.uuid4())
        imgurl=f'http://dynamic.gs1cn.org/office/captcha.jpg?uuid={random_uuid}'
        cot_img=session.get(imgurl).content
        captcha=get_yzm(cot_img)
        url='http://dynamic.gs1cn.org/office/codeSearch/api/domesticCode'
        params={
            "type":"2",
            "uuid":random_uuid,
            "code":pname,
            "captcha":captcha
        }
        res= session.post(url,headers=headers,json=params)
        jsdata= res.json()
        hits=jsdata['data']['hits']['hits']
        datas=[]
        for hit in hits:
            citem={}
            citem['_id']=hit['_id']
            citem['_score']=hit['_score']
            _source=hit['_source']
            for k,v in _source.items():
                if isinstance(v,str):
                    v=v.strip()
                citem[k]=v
            datas.append(citem)
        print(f'id:{pid},搜索名称:《{pname}》,共采集了{len(datas)}个数据')
        aff=0
        db=self.dbfra.get_conn()
        for data in datas:
            cid=data['_id']
            fname=data['firm_name']
            rs= db.query(f'select count(*) from {tab_info_name} where _id=%s and firm_name=%s',(cid,fname))
            if rs[0][0]==0:

                zdstr=','.join(data.keys())
                params=list(data.values())
                wstr=','.join([r'%s' for _ in params])
                sqlstr=f'insert into {tab_info_name}({zdstr}) values({wstr})'
                aff+=db.execute_command(sqlstr,params)
            else:
                if self.CJModeSelect.current()==0:
                    time.sleep(0.5)
                    continue
                elif self.CJModeSelect.current()==1:
                    zdstr=','.join([f'{k}=%s' for k in data.keys()])
                    params=list(data.values())
                    sqlstr=f'update {tab_info_name} set {zdstr} where _id=%s'
                    aff+=db.execute_command(sqlstr,params+[cid])

        db.close_connection()
        time.sleep(0.5)
        return pid,pname,aff

class DCFrame(Frame):
    def __init__(self,master,dbfra:DBFrame):
        Frame.__init__(self,master)
        self.dbfra=dbfra
        self.createWidgets()
    
    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='导出公司详细信息', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        f00=Frame(f1)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='每份文件最大行数:').pack(side=LEFT, padx=2, pady=2)
        
        self.CountIpt = Entry(f00,width=10)
        self.CountIpt.pack(side=LEFT, padx=2, pady=2)
        self.CountIpt.insert(0,1000000)
        self.btn_dc=Button(f00, text='导出为Excel', command=lambda : Thread(target=self.dc).start(),fg='#ffffff',bg='green')
        self.btn_dc.pack(side=LEFT, padx=6, pady=2)

    def dc(self):
        
        dcount=self.CountIpt.get().strip()
        if not dcount:
            messagebox.showinfo('提示','请输入最大导出')
        self.btn_dc.config(state=DISABLED)
        dcount=int(dcount)
        db = MySQLDB(DB_PATH)
        rs=db.query(f"SELECT COUNT(*) FROM {TABLE_NAME_INFO}")
        total_records = rs[0][0]

        if total_records>0:
            dirparh=f'导出结果_{getTimeStr()}'
            os.mkdir(dirparh)
            for offset in range(0, total_records, dcount):
                sqlstr=f'SELECT * FROM {TABLE_NAME_INFO} LIMIT {dcount} OFFSET {offset}'
                fname=sqlToExcel(f'{dirparh}/公司信息',sqlstr)
                print(f'成功导出:{fname}')
        aff=db.execute_command(f'update {TABLE_NAME_INFO} set daochu=daochu+1')
        db.close_connection()
        messagebox.showinfo('提示',f'导出完毕:{aff}')
        
        self.btn_dc.config(state=NORMAL)
            
# import tkinter
# list_win = tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('主页')
# HomeFrame(list_win).pack(fill=X, padx=3, pady=3)
# list_win.mainloop()